package com.xxl.job.executor.service.impl;

import com.xxl.job.core.log.XxlJobLogger;
import com.xxl.job.executor.common.util.DBUtils;
import com.xxl.job.executor.common.util.SpringContextHolder;
import com.xxl.job.executor.service.IInlistService;
import org.springframework.jdbc.datasource.DataSourceUtils;
import org.springframework.stereotype.Service;

import javax.sql.DataSource;
import java.sql.Connection;

@Service("inlistService")
public class InlistService implements IInlistService {
    private Connection connPre = null;


    /**
     *
     * @throws Exception
     */
    @Override
    public void transfer() throws Exception {
        XxlJobLogger.log("=====开始执行入口流水同步业务操作===");
        final String recordName = "binlist";
        final String recordNameETC = "etcbinlist";
        try {
            DataSource dataSourcePre = SpringContextHolder.getBean("dataSourcePre");
            connPre = DataSourceUtils.getConnection(dataSourcePre);
            connPre.setAutoCommit(false);

            String recordStr = DBUtils.getCurSynRecordNum(connPre, recordName);
            XxlJobLogger.log("[普通]MTC入口本次最大记录RECORDID为:" + recordStr);

            String recordStrETC = DBUtils.getCurSynRecordNum(connPre, recordNameETC);
            XxlJobLogger.log("[普通]ETC入口本次最大记录RECORDID为:" + recordStrETC);

            String dbCurTime = DBUtils.getDBCurTime(connPre);
            XxlJobLogger.log("[普通]本次同步截止时间为:" + dbCurTime);

            StringBuffer sql = new StringBuffer();
            long start = System.currentTimeMillis();
            sql.append("insert into TB_SYNC_TRC_ENPU_TMP(id,mediatype,obusign,terminaltransno,obuid,cardid,entime,vehicleid,vehicletype,vehicleclass," +
                    "tac,transfee,transtype,terminalno,signstatus,specialtype,entolllanehex,passid,vehiclesign,lanetype,operationmedia,stationno," +
                    "squadno,squaddate,operatorno,operatorname,transferstatus,receivetime,recordid,inserttime,batchno,laneNo) " +
                    "select ListNo id,mediatype,obusign,ElePsamTradeNo terminaltransno,EleObuID obuid,cardNo cardid,replace(to_char(OpTime,'yyyy-mm-dd hh24:mi:ss'),' ','T') entime,vehplate vehicleid,vehtype vehicletype,vehclass vehicleclass," +
                    "EleTac tac,transfee,transtype,ElePsamID terminalno,signstatus,specialtype,entolllanehex,passid,vehiclesign,lanetype,1 as operationmedia,stationno," +
                    "squadno,squaddate,operatorno,operatorname,1 transferstatus,null receivetime,SEQ_TB_SYNC_TRC_ENPU.NEXTVAL recordid,sysdate inserttime,null batchno,laneNo " +
                    " from BInList_ALL " +
                    " where inserttabletime>=to_date('" + recordStr + "','yyyy-mm-dd hh24:mi:ss') and inserttabletime<to_date('" + dbCurTime + "','yyyy-mm-dd hh24:mi:ss') ");
            Integer count = DBUtils.execute(connPre, sql.toString());
            XxlJobLogger.log("[普通]MTC入口本次从中台系统共同步(条):" + count+",共耗时(秒)："+(System.currentTimeMillis() - start)/1000);

            count = DBUtils.modifySynRecordNum(connPre, recordName, recordStr, dbCurTime);
            if (count == null || count != 1) {
                XxlJobLogger.log("[异常]MTC更新记录时间异常");
                throw new Exception("[异常]MTC更新记录时间异常");
            }
            XxlJobLogger.log("[普通]MTC更新记录时间为:" + dbCurTime);


            long start2 = System.currentTimeMillis();
            String sqlETC = "insert into TB_SYNC_TRC_ENPU_TMP(id,mediatype,obusign,terminaltransno,obuid,cardid,entime,vehicleid,vehicletype,vehicleclass," +
                    "tac,transfee,transtype,terminalno,signstatus,specialtype,entolllanehex,passid,vehiclesign,lanetype,operationmedia,stationno," +
                    "squadno,squaddate,operatorno,operatorname,transferstatus,receivetime,recordid,inserttime,batchno,laneNo) " +
                    "select ListNo id,mediatype,obusign,ElePsamTradeNo terminaltransno,EleObuID obuid,cardNo cardid,replace(to_char(OpTime,'yyyy-mm-dd hh24:mi:ss'),' ','T') entime,vehplate vehicleid,vehtype vehicletype,vehclass vehicleclass," +
                    "EleTac tac,transfee,transtype,ElePsamID terminalno,signstatus,specialtype,entolllanehex,passid,vehiclesign,lanetype,1 as operationmedia,stationno," +
                    "squadno,squaddate,operatorno,operatorname,1 transferstatus,sysdate receivetime,SEQ_TB_SYNC_TRC_ENPU.NEXTVAL recordid,sysdate inserttime,null batchno,laneNo" +
                    " from ETCBInList_ALL " +
                    " where inserttabletime>=to_date('" + recordStr + "','yyyy-mm-dd hh24:mi:ss') and inserttabletime<to_date('" + dbCurTime + "','yyyy-mm-dd hh24:mi:ss') ";
            count = DBUtils.execute(connPre, sqlETC);
            XxlJobLogger.log("[普通]ETC入口本次从中台系统共同步(条):" + count+",共耗时(秒)："+(System.currentTimeMillis() - start2)/1000);

            count = DBUtils.modifySynRecordNum(connPre, recordNameETC, recordStrETC, dbCurTime);
            if (count == null || count != 1) {
                XxlJobLogger.log("[异常]ETC更新记录时间异常");
                throw new Exception("[异常]ETC更新记录时间异常");
            }
            XxlJobLogger.log("[普通]ETC更新记录时间为:" + dbCurTime);

            XxlJobLogger.log("将入口流水从临时表中插入到入口流水正式表");
            long start3 = System.currentTimeMillis();
            String sqlStr = "insert into TB_SYNC_TRC_ENPU(id,mediatype,obusign,terminaltransno,obuid,cardid,entime,vehicleid,vehicletype,vehicleclass," +
                    "tac,transfee,transtype,terminalno,signstatus,specialtype,entolllanehex,passid,vehiclesign,lanetype,operationmedia,stationno," +
                    "squadno,squaddate,operatorno,operatorname,transferstatus,receivetime,recordid,inserttime,batchno,laneNo) " +
                    "select id,mediatype,obusign,terminaltransno,obuid,cardid,entime,vehicleid,vehicletype,vehicleclass," +
                    "tac,transfee,transtype,terminalno,signstatus,specialtype,entolllanehex,passid,vehiclesign,lanetype,operationmedia,stationno," +
                    "squadno,squaddate,operatorno,operatorname,transferstatus,receivetime,recordid,inserttime,batchno,laneNo" +
                    " from TB_SYNC_TRC_ENPU_TMP";
            count = DBUtils.execute(connPre, sqlStr);
            XxlJobLogger.log("[普通]共同步入口流水条数:" + count+",共耗时(秒)："+(System.currentTimeMillis() - start2)/1000);
            connPre.commit();

            //统计入口车流量报表
            statisEnCarFlow(connPre);

            connPre.commit();
            XxlJobLogger.log("=====完成执行入口流水同步业务操作===");
        }catch(Exception e){
            if (connPre != null) {
                try {
                    e.printStackTrace();
                    connPre.rollback();
                    XxlJobLogger.log("回滚成功.");
                } catch (Exception e1) {
                    e1.printStackTrace();
                }
            }
            XxlJobLogger.log("程序运行异常："+e.getMessage());
            throw e;
        }finally{
            if (connPre != null) {
                try {
                    connPre.setAutoCommit(true);
                    connPre.close();
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }


    /**
     * 统计入口车流量信息
     * @param connPre
     * @throws Exception
     */
    private void statisEnCarFlow(Connection connPre) throws Exception{
        int count = 0;
        try{
            XxlJobLogger.log("[入口流水统计]统计开始");
            //合并临时完整性表到正式表
            String statSql = "MERGE INTO TB_STATIS_STATION_ENCARFLOW A USING V_SYNC_TRC_ENPU B " +
                    "ON (A.TRADE_DATE=B.TRADE_DATE and A.STATION_ID=B.STATION_ID) " +
                    "WHEN MATCHED THEN " +
                    "UPDATE SET A.VEHICLE_FLOW_1=nvl(B.VEHICLE_FLOW_1,0)+nvl(A.VEHICLE_FLOW_1,0)," +
                    "A.VEHICLE_FLOW_2=nvl(B.VEHICLE_FLOW_2,0)+nvl(A.VEHICLE_FLOW_2,0)," +
                    "A.VEHICLE_FLOW_3=nvl(B.VEHICLE_FLOW_3,0)+nvl(A.VEHICLE_FLOW_3,0)," +
                    "A.VEHICLE_FLOW_4=nvl(B.VEHICLE_FLOW_4,0)+nvl(A.VEHICLE_FLOW_4,0)," +
                    "A.VEHICLE_FLOW_11=nvl(B.VEHICLE_FLOW_11,0)+nvl(A.VEHICLE_FLOW_11,0)," +
                    "A.VEHICLE_FLOW_12=nvl(B.VEHICLE_FLOW_12,0)+nvl(A.VEHICLE_FLOW_12,0)," +
                    "A.VEHICLE_FLOW_13=nvl(B.VEHICLE_FLOW_13,0)+nvl(A.VEHICLE_FLOW_13,0)," +
                    "A.VEHICLE_FLOW_14=nvl(B.VEHICLE_FLOW_14,0)+nvl(A.VEHICLE_FLOW_14,0)," +
                    "A.VEHICLE_FLOW_15=nvl(B.VEHICLE_FLOW_15,0)+nvl(A.VEHICLE_FLOW_15,0)," +
                    "A.VEHICLE_FLOW_16=nvl(B.VEHICLE_FLOW_16,0)+nvl(A.VEHICLE_FLOW_16,0)," +
                    "A.VEHICLE_FLOW_21=nvl(B.VEHICLE_FLOW_21,0)+nvl(A.VEHICLE_FLOW_21,0)," +
                    "A.VEHICLE_FLOW_22=nvl(B.VEHICLE_FLOW_22,0)+nvl(A.VEHICLE_FLOW_22,0)," +
                    "A.VEHICLE_FLOW_23=nvl(B.VEHICLE_FLOW_23,0)+nvl(A.VEHICLE_FLOW_23,0)," +
                    "A.VEHICLE_FLOW_24=nvl(B.VEHICLE_FLOW_24,0)+nvl(A.VEHICLE_FLOW_24,0)," +
                    "A.VEHICLE_FLOW_25=nvl(B.VEHICLE_FLOW_25,0)+nvl(A.VEHICLE_FLOW_25,0)," +
                    "A.VEHICLE_FLOW_26=nvl(B.VEHICLE_FLOW_26,0)+nvl(A.VEHICLE_FLOW_26,0)" +
                    ",A.UPDATE_TIME=sysdate " +
                    "WHEN NOT MATCHED THEN " +
                    "INSERT (A.STATION_ID,A.TRADE_DATE,A.ROAD_ID," +
                    "  A.VEHICLE_FLOW_1,A.VEHICLE_FLOW_2,A.VEHICLE_FLOW_3,A.VEHICLE_FLOW_4,A.VEHICLE_FLOW_11,A.VEHICLE_FLOW_12," +
                    "  A.VEHICLE_FLOW_13,A.VEHICLE_FLOW_14,A.VEHICLE_FLOW_15,A.VEHICLE_FLOW_16,A.VEHICLE_FLOW_21,A.VEHICLE_FLOW_22," +
                    "  A.VEHICLE_FLOW_23,A.VEHICLE_FLOW_24,A.VEHICLE_FLOW_25,A.VEHICLE_FLOW_26,A.INSERT_TIME,A.UPDATE_TIME) " +
                    "VALUES(B.STATION_ID,B.TRADE_DATE,B.ROAD_ID,B.VEHICLE_FLOW_1,B.VEHICLE_FLOW_2,B.VEHICLE_FLOW_3,B.VEHICLE_FLOW_4," +
                    "B.VEHICLE_FLOW_11,B.VEHICLE_FLOW_12,B.VEHICLE_FLOW_13,B.VEHICLE_FLOW_14,B.VEHICLE_FLOW_15,B.VEHICLE_FLOW_16," +
                    "B.VEHICLE_FLOW_21,B.VEHICLE_FLOW_22,B.VEHICLE_FLOW_23,B.VEHICLE_FLOW_24,B.VEHICLE_FLOW_25,B.VEHICLE_FLOW_26," +
                    "sysdate,null)";
            count = DBUtils.execute(connPre, statSql);
            XxlJobLogger.log("[入口流水统计]统计完成");

            XxlJobLogger.log("=====开始执行删除入口流水临时表操作===");
            String delSql = "delete from TB_SYNC_TRC_ENPU_TMP where 1=1";
            count = DBUtils.execute(connPre, delSql);
        }catch (Exception e){
            e.printStackTrace();
            throw e;
        }
    }
}

